import pandas as pd
import numpy as np
import datetime
from sklearn.linear_model import LinearRegression
# 钢种分类：
# 1、出钢记号：'IH2554A2'
# 2、出钢记号前两位：'IH' 但不是'IH2554A2'
# 3、出钢记号前两位：'IW'
# 4、计划钢种目标S：[0,15]  出钢记号前两位不是：'IW' 'IH'
# 5、计划钢种目标S：(15,20]  出钢记号前两位不是：'IW' 'IH'
# 6、计划钢种目标S：(20,30]  出钢记号前两位不是：'IW' 'IH'
# 7、计划钢种目标S：(30,50]  出钢记号前两位不是：'IW' 'IH'
# 8、计划钢种目标S：(50,100]  出钢记号前两位不是：'IW' 'IH'
# 9、计划钢种目标S：(100,150)  出钢记号前两位不是：'IW' 'IH'
# 10、计划钢种目标S：[150,180)  出钢记号前两位不是：'IW' 'IH'
# 11、计划钢种目标S：[180,250]  出钢记号前两位不是：'IW' 'IH'
# 12、计划钢种目标S：(250,99999]  出钢记号前两位不是：'IW' 'IH'
# 数据拼接
# 数据读取
# 数据清洗
# 根据分类规则选择钢种
# 查看最新数据对应KR后要求,最近的众数
# 使用统计的单耗，时间分段线性函数
# 计算出传入铁水S对应的CAO单耗以及脱硫时间，根据历史数据拟合时间与温降的系数，
# 计算出各成本，总成本
message = ''
p_st_no = 'IH2554A2'
p_aim_st_s = 20
st_no = p_st_no
aim_st_s = p_aim_st_s
if st_no == 'IH2554A2':
    group = 1
elif st_no[:2] == 'IH' and st_no != 'IH2554A2':
    group = 2
elif st_no[:2] == 'IW':
    group = 3
elif aim_st_s <= 15 and st_no[:2] not in ['IH', 'IW']:
    group = 4
elif aim_st_s <= 20 and aim_st_s > 15 and st_no[:2] not in ['IH', 'IW']:
    group = 5
elif aim_st_s <= 30 and aim_st_s > 20 and st_no[:2] not in ['IH', 'IW']:
    group = 6
elif aim_st_s <= 50 and aim_st_s > 30 and st_no[:2] not in ['IH', 'IW']:
    group = 7
elif aim_st_s <= 100 and aim_st_s > 50 and st_no[:2] not in ['IH', 'IW']:
    group = 8
elif aim_st_s < 150 and aim_st_s > 100 and st_no[:2] not in ['IH', 'IW']:
    group = 9
elif aim_st_s < 180 and aim_st_s >= 150 and st_no[:2] not in ['IH', 'IW']:
    group = 10
elif aim_st_s <= 250 and aim_st_s >= 180 and st_no[:2] not in ['IH', 'IW']:
    group = 11
else:
    group = 12
xlsx_name = 'D:/repos/sicost/group'+str(int(group))+'_new.xlsx'
df = pd.read_excel(xlsx_name)
if df.empty is True:
    message = '该分组无数据'
df.columns = df.columns.str.upper()
df['PROD_DATE'] = df['PROD_DATE'].astype(str)
start = datetime.datetime.now()
for i in range(0, 10):
    mode_fret_s_aim = 0
    delta_day = int((i+1)*10)
    p_day_1 = (start - datetime.timedelta(days=delta_day)).strftime("%Y%m%d")
    print(p_day_1)
    df1 = df[(df['PROD_DATE'] >= p_day_1)]
    if df1.empty is False:
        mode_fret_s_aim = df['PRET_S_AIM'].mode()[0]
        break
    else:
        continue
print(mode_fret_s_aim)
column_aim_tmp = 'AIM_' + str(int(mode_fret_s_aim))
if st_no[:2] == 'IW':
    column_aim_tmp = 'IW'
column_aim_list = ['IW', 'AIM_14', 'AIM_24', 'AIM_34', 'AIM_54']
if column_aim_tmp not in column_aim_list:
    column_aim_tmp = 'AIM_14'
    mode_fret_s_aim = 14
recv_s_max_list1 = [120, 140, 160, 180, 200, 300, 400, 500]
recv_s_max_list2 = [0, 200, 300, 400, 500]
# col_list = []
# col_list.append('PROD_DATE')
# col_list.append('ST_NO')
# col_list.append('AIM_ST_S')
# col_list.append('ACT_ST_S')
# col_list.append('PRET_S_AIM')
# col_list.append('IRON_WT_TOTAL')
# col_list.append('STTEMP_AFIRON')
# col_list.append('RECV_S')
# col_list.append('AFTEMP_AFIRON')
# col_list.append('CAO_LOSS_SUM')
# col_list.append('DES_WHISK_DEPTH1')
# print(col_list)
# xlsx_name = 'D:/repos/sicost/allgroup_new.xlsx'
# df_all = pd.read_excel(xlsx_name)
# df_all.columns = df_all.columns.str.upper()
# df_all['PROD_DATE'] = df_all['PROD_DATE'].astype(str)
# df_all1 = df_all[df_all['ST_NO'].str[0:2] == 'IW']
# df_all2 = df_all[df_all['ST_NO'].str[0:2] != 'IW']
# if st_no[:2] == 'IW':
#     df2 = df_all1
# else:
#     df2 = df_all2
# df3 = df2[col_list]
# df3 = df3[(df3['PROD_DATE'] >= '20230101')]
# df3 = df3.reset_index(drop=True)
# df3['DELTA'] = abs(df3['AIM_ST_S'] - df3['ACT_ST_S'])
# #数据清洗1
# df3_clean1 = df3[(df3['DELTA'] <= 100)]
# df3_clean1.drop(['ACT_ST_S'], axis=1, inplace=True)
# df3_clean1.drop(['DELTA'], axis=1, inplace=True)
# #数据清洗2
# def clean_data(df, gamma):
#     column_name_list = df.columns.tolist()
#     column_name_list.remove('PROD_DATE')
#     column_name_list.remove('ST_NO')
#     column_name_list.remove('AIM_ST_S')
#     column_name_num = len(column_name_list)
#     clean_str_start = 'df_new = df['
#     clean_str_end = ']'
#     ldict1 = {}
#     for i in range(0, column_name_num):
#         # print(i)
#         # print(column_name_list[i])
#         column_name_tmp = column_name_list[i]
#         exec('''clean_str3 = "(df['{}'] > 0)"'''.format(column_name_tmp), locals(), ldict1)
#         clean_str3 = ldict1["clean_str3"]
#         if i == 0:
#             clean_str_start = clean_str_start + clean_str3
#         else:
#             clean_str_start = clean_str_start + ' & ' + clean_str3
#     clean_str = clean_str_start + clean_str_end
#     # print(clean_str)
#     exec(clean_str, locals(), ldict1)
#     df_new = ldict1["df_new"]
#     df_new = df_new.reset_index(drop=True)
#     return df_new
# gamma = 1.5
# df3_clean2 = clean_data(df3_clean1, gamma)
# df3_clean2['DH'] = df3_clean2['CAO_LOSS_SUM'] / df3_clean2['IRON_WT_TOTAL'] * 100
# df3_clean2['TEMP'] = df3_clean2['STTEMP_AFIRON'] - df3_clean2['AFTEMP_AFIRON']
# #数据清洗3
# df3_clean3 = df3_clean2[(df3_clean2['TEMP'] > 0)]
# df3_clean3 = df3_clean3.reset_index(drop=True)
# df3_clean3.drop(['STTEMP_AFIRON'], axis=1, inplace=True)
# df3_clean3.drop(['AFTEMP_AFIRON'], axis=1, inplace=True)
if st_no[:2] == 'IW':
    xlsx_name = 'D:/repos/sicost/clean3_2.xlsx'
    df4 = pd.read_excel(xlsx_name)
    df4.columns = df4.columns.str.upper()
    df4['PROD_DATE'] = df4['PROD_DATE'].astype(str)
else:
    xlsx_name = 'D:/repos/sicost/clean3_1.xlsx'
    df4 = pd.read_excel(xlsx_name)
    df4.columns = df4.columns.str.upper()
    df4['PROD_DATE'] = df4['PROD_DATE'].astype(str)
    df4 = df4[df4['PRET_S_AIM']==mode_fret_s_aim]
    df4 = df4.reset_index(drop=True)
gamma = 1.5
q1 = df4['RECV_S'].quantile(0.25)
q3 = df4['RECV_S'].quantile(0.75)
iqr_val = q3 - q1
q1_2 = df4['DH'].quantile(0.25)
q3_2 = df4['DH'].quantile(0.75)
iqr_val_2 = q3_2 - q1_2
q1_3 = df4['DES_WHISK_DEPTH1'].quantile(0.25)
q3_3 = df4['DES_WHISK_DEPTH1'].quantile(0.75)
iqr_val_3 = q3_3 - q1_3
df5 = df4[(df4['RECV_S'] <= q3 + gamma * iqr_val) & (df4['RECV_S'] >= q1 - gamma * iqr_val)
        & (df4['DH'] <= q3_2 + gamma * iqr_val_2) & (df4['DH'] >= q1_2 - gamma * iqr_val_2)
        & (df4['DES_WHISK_DEPTH1'] <= q3_3 + gamma * iqr_val_3) & (df4['DES_WHISK_DEPTH1'] >= q1_3 - gamma * iqr_val_3)]
df5 = df5.reset_index(drop=True)
df_out1 = pd.DataFrame(
                columns=['RECV_S_MAX', column_aim_tmp])
dict = {}
# xlsx_name = 'D:/repos/sicost/二炼钢脱硫模型单耗.xlsx'
# df6 = pd.read_excel(xlsx_name)
# df6.columns = df6.columns.str.upper()
for i in range(0,len(recv_s_max_list1)):
    print(recv_s_max_list1[i])
    if i == 0:
        interval = recv_s_max_list1[i+1]-recv_s_max_list1[i]
    else:
        interval = recv_s_max_list1[i]-recv_s_max_list1[i-1]
    min_tmp = recv_s_max_list1[i] - interval / 10
    max_tmp = recv_s_max_list1[i] + interval / 10
    df5_copy = df5.copy()
    df5_copy['DELTA_RECV_S'] = abs(df5_copy['RECV_S'] - recv_s_max_list1[i])
    df5_copy_sorted = df5_copy.sort_values(by='DELTA_RECV_S')
    # , ascending = False
    df5_copy_sorted = df5_copy_sorted.reset_index(drop=True)
    df5_tmp2 = df5_copy_sorted.head(10)
    df5_tmp1 = df5_copy[(df5_copy['RECV_S'] <= max_tmp) & (df5_copy['RECV_S'] >= min_tmp)]
    df5_tmp3 = df5_copy[(df5_copy['RECV_S'] == recv_s_max_list1[i])]
    row_count1 = len(df5_tmp1)
    row_count3 = len(df5_tmp3)
    if row_count3 > 0:
        y1_pred = df5_tmp3['DH'].mean()
        print(y1_pred)
        dict['RECV_S_MAX'] = recv_s_max_list1[i]
        dict[column_aim_tmp] = y1_pred
        new_row = pd.Series(dict)
        df_out1 = df_out1.append(new_row, ignore_index=True)
    else:
        if row_count1 <= 5:
            df5_tmp = df5_tmp2
        else:
            df5_tmp = df5_tmp1
        model = LinearRegression()
        X = df5_tmp['RECV_S'].values.reshape(-1, 1)
        y = df5_tmp['DH'].values
        model.fit(X, y)
        list_tmp = []
        list_tmp.append(recv_s_max_list1[i])
        array_tmp = np.array(list_tmp).reshape(-1, 1)
        y_pred = model.predict(array_tmp)
        # print(y_pred)
        y1_pred = y_pred[0]
        # y1_pred = df5_tmp['DH'].mean()
        print(y1_pred)
        dict['RECV_S_MAX'] = recv_s_max_list1[i]
        dict[column_aim_tmp] = y1_pred
        new_row = pd.Series(dict)
        df_out1 = df_out1.append(new_row, ignore_index=True)
df_out2 = pd.DataFrame(
                columns=['RECV_S_MAX', column_aim_tmp])
dict = {}
for i in range(0,len(recv_s_max_list2)):
    print(recv_s_max_list2[i])
    if i == 0:
        interval = recv_s_max_list2[i+1]-recv_s_max_list2[i]
    else:
        interval = recv_s_max_list2[i]-recv_s_max_list2[i-1]
    min_tmp = recv_s_max_list2[i] - interval / 10
    max_tmp = recv_s_max_list2[i] + interval / 10
    df5_copy = df5.copy()
    df5_copy['DELTA_RECV_S'] = abs(df5_copy['RECV_S'] - recv_s_max_list2[i])
    df5_copy_sorted = df5_copy.sort_values(by='DELTA_RECV_S')
    # , ascending = False
    df5_copy_sorted = df5_copy_sorted.reset_index(drop=True)
    df5_tmp2 = df5_copy_sorted.head(10)
    df5_tmp1 = df5_copy[(df5_copy['RECV_S'] <= max_tmp) & (df5_copy['RECV_S'] >= min_tmp)]
    df5_tmp3 = df5_copy[(df5_copy['RECV_S'] == recv_s_max_list2[i])]
    row_count1 = len(df5_tmp1)
    row_count3 = len(df5_tmp3)
    if row_count3 > 0:
        y1_pred = df5_tmp3['DES_WHISK_DEPTH1'].mean()
        print(y1_pred)
        dict['RECV_S_MAX'] = recv_s_max_list2[i]
        dict[column_aim_tmp] = y1_pred
        new_row = pd.Series(dict)
        df_out2 = df_out2.append(new_row, ignore_index=True)
    else:
        if row_count1 <= 5:
            df5_tmp = df5_tmp2
        else:
            df5_tmp = df5_tmp1
        model = LinearRegression()
        X = df5_tmp['RECV_S'].values.reshape(-1, 1)
        y = df5_tmp['DES_WHISK_DEPTH1'].values
        model.fit(X, y)
        list_tmp = []
        list_tmp.append(recv_s_max_list2[i])
        array_tmp = np.array(list_tmp).reshape(-1, 1)
        y_pred = model.predict(array_tmp)
        # print(y_pred)
        y1_pred = y_pred[0]
        # y1_pred = df5_tmp['DH'].mean()
        print(y1_pred)
        dict['RECV_S_MAX'] = recv_s_max_list1[i]
        dict[column_aim_tmp] = y1_pred
        new_row = pd.Series(dict)
        df_out2 = df_out2.append(new_row, ignore_index=True)

print(df_out1)
print('finish')

